package DBLayer;
import java.sql.*;
import ModelLayer.Invoice;
import java.util.ArrayList;


public class DBInvoice implements IFDBInvoice {
    private  Connection con;
    PreparedStatement pStmtSelect;
    
    public DBInvoice() {
        con = DBConnection.getInstance().getDBcon();
    }
    
    public ArrayList<Invoice> getAllInvoice(boolean retriveAssociation)
    {
        return miscWhere("", retriveAssociation);
    }
    
    public Invoice findInvoice(int invoiceNo)
    {
        Invoice invoiceObj = new Invoice();
  
        invoiceObj = findWithPreQuery(invoiceNo);
        return invoiceObj;
    }
   
    private Invoice singleWhere(String wClause, boolean retrieveAssociation)
    {	
        ResultSet results;
        Invoice invoiceObj = new Invoice();
        String query =  buildQuery(wClause);
        System.out.println("DbInvoice -singelWhere " + query); 
        try{ 
	   Statement stmt = con.createStatement();
	   stmt.setQueryTimeout(5);
	   results = stmt.executeQuery(query);
	   int snr=0;
	   if( results.next() ){
	       invoiceObj = buildInvoice(results);
               
               
           }
	   stmt.close();
	}	
	catch(Exception e){
	   System.out.println("Query exception - select project : "+e);
	   
        }
	return invoiceObj;

    
    }
    
    private ArrayList<Invoice> miscWhere(String wClause, boolean retrieveAssociation)
	{
            ResultSet results;
	    ArrayList<Invoice> list = new ArrayList<Invoice>();	
		
	    String query =  buildQuery(wClause);
  
            try{ 
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
		int snr=0;
		while( results.next() ){
	     	 Invoice invObj = new Invoice();
		 invObj = buildInvoice(results);	
                 list.add(invObj);	
		}
                 stmt.close();     
                 
			
		}	
	 	catch(Exception e){
	 		System.out.println("Query exception - select: "+e);
			
	 	}
		return list;
	}
    
    private Invoice buildInvoice(ResultSet results)
    {
       Invoice invoiceObj = new Invoice();
       
         try{
               invoiceObj.setInvoiceNo(results.getInt("invoiceNo"));
               invoiceObj.setPaymentDate(results.getString("paymentDate"));
               invoiceObj.setAmount(results.getDouble("amount"));
               
          }
         catch(Exception e)
         {
             System.out.println("building project object");
         }
        
        return invoiceObj;
        
    }
    
	private String buildQuery(String wClause)
	{
            String query="SELECT invoiceNo, paymentDate, amount FROM invoice"; 
            
           

		if (wClause.length()>0)
			query = query + " where " + wClause;
			
		return query;
	}
        
        public Invoice findWithPreQuery( int invoiceNo)
        {   System.out.println("Find with preCompiled");
            if(pStmtSelect == null){
               pStmtSelect =  getPreparedStmt();          
            }
            Invoice invObj = null;
            ResultSet results;
            try{
           
            pStmtSelect.setInt(1,invoiceNo);
            results = pStmtSelect.executeQuery();
            if(results.next()){
                 invObj = buildInvoice(results);	
            }
            }
            catch(Exception e){
                e.getMessage();
            }
            return invObj;
       }
        private PreparedStatement getPreparedStmt()
        {
           try{ 
            String query = "select invoiceNo, paymentDate, amount from invoice" +
                           " where invoiceNo = ?";
            System.out.println("Preparestatement " + query);
            pStmtSelect = con.prepareStatement(query);
            
           }
           catch(Exception e){
                e.getMessage();
            }
           return pStmtSelect;
        }
        
        public int insertInvoice(Invoice invObj)
        { 
            int rc = -1; 
            PreparedStatement pstmt = null;
             String insert = "insert into project (invoiceNo, paymentDate, amount)"+
                     "values (?,?,?)";
             System.out.println(insert);
             try{
                pstmt = con.prepareStatement(insert);
                pstmt.setInt(1,invObj.getInvoiceNo());
                pstmt.setString(2,invObj.getPaymentDate());
                pstmt.setDouble(3,invObj.getAmount());
                rc = pstmt.executeUpdate();
                                 
                
             }
             catch(SQLException sqlE)
             {
                 System.out.println("SQL Error");
                 System.out.println(sqlE.getMessage());   
             }
             catch(Exception e){
                 e.getMessage();
                 
             }
             return rc;
             
        }
        public int deleteInvoice(int invoiceNo)
        {
           int rc = -1;
           PreparedStatement pstmt = null;
           String delete = "delete from invoice where invoiceNo = ?";
           System.out.println(delete);
             try{
                pstmt = con.prepareStatement(delete);
                pstmt.setInt(1,invoiceNo);
                rc = pstmt.executeUpdate();                       
             }
             catch(SQLException sqlE)
             {
                 System.out.println("SQL Error");
                 System.out.println(sqlE.getMessage());   
             }
             catch(Exception e){
                 e.getMessage();
                 
             }
            return rc;
        }
        
        public int updateInvoice(Invoice inv)
	{
		Invoice invObj  = inv;
		int rc=-1;

		String query="UPDATE Invoice SET "+
		 	  "paymentDate ='"+ invObj.getPaymentDate()+"', "+
                          "address ='"+ invObj.getAmount() + "', " +
		          " WHERE invoiceNo = '"+ invObj.getInvoiceNo() + "'";
                System.out.println("Update query:" + query);
  		try{ 
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}
	 	catch(Exception ex){
	 	 	System.out.println("Update exception in invoice db: "+ex);
	  	}
		return(rc);
	}
        }